﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using Model;
using MySql.Data.MySqlClient;

namespace DAL
{
    public class SOP_ProductKeyDAL
    {
        #region 添加
        /// <summary>
        /// 添加
        /// </summary>
        /// <param name="ProductKey"></param>
        /// <returns>5成功，-5失败，-10000系统错误</returns>
        public int AddProductKey(ProductKey Ent)
        {
            using (MySqlConnection conn = DAL.SqlHelper.Settings.GetSOPMySqlConnection(true))
            {
                try
                {
                    MySqlCommand cmd = new MySqlCommand("P_AddProductKeyByBossID_Arcyun", conn);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add("@VipUserID_Para", MySqlDbType.VarChar, 12).Value = Ent.VipUserID;//call P_GetNewUserId(10,0)  "userid":
                    cmd.Parameters.Add("@ProductUserName_Para", MySqlDbType.VarChar, 32).Value = Ent.ProductUserName;//call P_GetNewUserId(10,0)  "username10":
                    cmd.Parameters.Add("@CompanyName_Para", MySqlDbType.VarChar, 20).Value = Ent.CompanyName;//商家名称
                    cmd.Parameters.Add("@StoreName_Para", MySqlDbType.VarChar, 128).Value = Ent.StoreName;//商家名称
                    cmd.Parameters.Add("@EMail_Para", MySqlDbType.VarChar, 255).Value = Ent.EMail;//邮箱
                    cmd.Parameters.Add("@LinkMan_Para", MySqlDbType.VarChar, 32).Value = Ent.LinkMan;//联系人
                    cmd.Parameters.Add("@LinkTel_Para", MySqlDbType.VarChar, 13).Value = Ent.LinkTel;//联系电话
                    cmd.Parameters.Add("@OpenType_Para", MySqlDbType.Int32).Value = Ent.OpenType;//开户类型，默认20
                    cmd.Parameters.Add("@BrandType_Para", MySqlDbType.Int32).Value = Ent.BrandType;//默认为10，5:代表多品牌数据库，10:代表单品牌数据库
                    cmd.Parameters.Add("@BossType_Para", MySqlDbType.Int32).Value = Ent.BossType;//商户类型 10：多品牌商户（老的多品牌）；10：单品牌商户；15：多品牌商户（针对单品牌里面创建的多品牌）
                    cmd.Parameters.Add("@Password_Para", MySqlDbType.VarChar, 8).Value = Ent.Password;//c831b04d
                    cmd.Parameters.Add("@UserType_Para", MySqlDbType.Int32).Value = Ent.UserType;//10
                    cmd.Parameters.Add("@ServiceEndTime_Para", MySqlDbType.DateTime).Value = Ent.ServiceEndTime;//当前时间20年后
                    cmd.Parameters.Add("@RegisterDate_Para", MySqlDbType.DateTime).Value = Ent.RegisterDate;//当前时间
                    cmd.Parameters.Add("@AddTime_Para", MySqlDbType.DateTime).Value = Ent.AddTime;//当前时间
                    cmd.Parameters.Add("@UpdateTime_Para", MySqlDbType.DateTime).Value = Ent.UpdateTime;//当前时间
                    cmd.Parameters.Add("@StoreNum_Para", MySqlDbType.Int32).Value = Ent.StoreNum;//专柜数
                    cmd.Parameters.Add("@LoginAccount_Para", MySqlDbType.VarChar, 32).Value = Ent.LoginAccount;//登陆账号 ProductUserName
                    cmd.Parameters.Add("@StoreLable_Para", MySqlDbType.Int32).Value = Ent.StoreLable;//默认5；5：专柜，10门店，15药店
                    cmd.Parameters.Add("@CheckStatus_Para", MySqlDbType.Int32).Value = Ent.CheckStatus;//5
                    cmd.Parameters.Add("@StoreActiveStatus_Para", MySqlDbType.Int32).Value = Ent.StoreActiveStatus;//10
                    cmd.Parameters.Add("@BossVersionType_Para", MySqlDbType.Int32).Value = Ent.BossVersionType;//默认5；商户版本 5：精品店版，10：连锁版，15：品牌版
                    cmd.Parameters.Add("@CompanyCode_Para", MySqlDbType.VarChar, 5).Value = Ent.CompanyCode;//品牌识别码
                    cmd.Parameters.Add("@VipUserStatus_Para", MySqlDbType.Int32).Value = Ent.VipUserStatus;//5
                    cmd.Parameters.Add("@CompanyId_Para", MySqlDbType.Int32).Value = Ent.CompanyId;
                    cmd.Parameters.Add("@UserId_Para", MySqlDbType.Int32).Value = Ent.UserId;
                    cmd.Parameters.Add("@ContactQQ_Para", MySqlDbType.VarChar, 30).Value = Ent.ContactQQ;
                    cmd.Parameters.Add("@SrcFlag_Para", MySqlDbType.Int32).Value = Ent.SrcFlag;

                    cmd.Parameters.Add("@AgencyID_Para", MySqlDbType.Int32).Value = Ent.AgencyID;//不用了，置0
                    cmd.Parameters.Add("@AgencyName_Para", MySqlDbType.VarChar, 32).Value = Ent.AgencyName;//不用了，置""
                    cmd.Parameters.Add("@BarCode_Para", MySqlDbType.VarChar, 20).Value = Ent.BarCode;//不用了，置""
                    cmd.Parameters.Add("@KeyBarcode_Para", MySqlDbType.VarChar, 13).Value = Ent.KeyBarcode;//null
                    cmd.Parameters.Add("@AuthKey_Para", MySqlDbType.VarChar, 300).Value = Ent.AuthKey;//null
                    cmd.Parameters.Add("@StoreID_Para", MySqlDbType.Int32).Value = Ent.StoreID;//0
                    cmd.Parameters.Add("@AreaSize_Para", MySqlDbType.Decimal).Value = Ent.AreaSize;//0
                    cmd.Parameters.Add("@AreaID_Para", MySqlDbType.Int32).Value = Ent.AreaID;//0
                    cmd.Parameters.Add("@AreaName_Para", MySqlDbType.VarChar, 255).Value = Ent.AreaName;//""
                    cmd.Parameters.Add("@Addresses_Para", MySqlDbType.VarChar, 50).Value = Ent.Addresses;//""
                    cmd.Parameters.Add("@PostCode_Para", MySqlDbType.VarChar, 10).Value = Ent.PostCode;//null
                    cmd.Parameters.Add("@LinkMobile_Para", MySqlDbType.Int32).Value = Ent.LinkMobile;//""
                    cmd.Parameters.Add("@EmergencyContact_Para", MySqlDbType.VarChar, 32).Value = Ent.EmergencyContact;//""
                    cmd.Parameters.Add("@EmergencyTelephone_Para", MySqlDbType.VarChar, 13).Value = Ent.EmergencyTelephone;//""
                    cmd.Parameters.Add("@DelayType_Para", MySqlDbType.Int32).Value = Ent.DelayType;//0
                    cmd.Parameters.Add("@ProductType_Para", MySqlDbType.Int32).Value = Ent.ProductType;//0
                    cmd.Parameters.Add("@AllotStatus_Para", MySqlDbType.Int32).Value = Ent.AllotStatus;//0
                    cmd.Parameters.Add("@ServiceMonth_Para", MySqlDbType.Int32).Value = Ent.ServiceMonth;//0
                    cmd.Parameters.Add("@Remark_Para", MySqlDbType.VarChar, 255).Value = Ent.Remark;//""
                    cmd.Parameters.Add("@UpdateOperatorId_Para", MySqlDbType.Int32).Value = Ent.UpdateOperatorId;//0
                    cmd.Parameters.Add("@UpdateOperatorName_Para", MySqlDbType.VarChar, 32).Value = Ent.UpdateOperatorName;//""
                    cmd.Parameters.Add("@OperatorId_Para", MySqlDbType.Int32).Value = Ent.OperatorId;//0
                    cmd.Parameters.Add("@OperatorName_Para", MySqlDbType.VarChar, 32).Value = Ent.OperatorName;//""

                    cmd.Parameters.Add("@IsSuccess_Para", MySqlDbType.Int32).Value = 0;
                    cmd.Parameters["@IsSuccess_Para"].Direction = ParameterDirection.Output;
                    if (conn.State == ConnectionState.Closed)
                    {
                        conn.Open();
                    }
                    cmd.ExecuteNonQuery();
                    Object ObjRtID = cmd.Parameters["@IsSuccess_Para"].Value;
                    return ObjRtID == null || ObjRtID.ToString() == string.Empty ? -5 : (int)ObjRtID;

                }
                catch (Exception ex)
                {
                    ActCommon.Log.WriteLog("AddProductKey(date:'"+System.DateTime.Now+"'),error:"+ex.Message, "AddCompany");
#if DEBUG
                    throw ex;
#else
                    return -10000;
#endif
                }
                finally
                {
                    conn.Close();
                }
            }
        }
        /// <summary>
        /// 开户审核
        /// </summary>
        /// <param name="CheckerID"></param>
        /// <param name="CheckPwd"></param>
        /// <param name="ID"></param>
        /// <param name="Status"></param>
        /// <param name="Reason"></param>
        /// <returns></returns>
        public int SetBrandCompanyOpenAccountBillCheck(int billID, ProductKey Ent)
        {

            int result = SyncPOSInfoFromSOP(Ent);

            if (result == 5)//当信息同步到SOP成功时
            {
                using (MySqlConnection conn = DAL.SqlHelper.Settings.GetSOPMySqlConnection(false))
                {
                    if (conn.State == ConnectionState.Closed)
                    {
                        conn.Open();
                    }
                    MySqlTransaction trans = conn.BeginTransaction();
                    try
                    {
                        int result2 = 0;
                        int exeCount = 0;
                        while (exeCount <= 1 && result2 <= 0) //如果执行失败，再执行一次
                        {
                            MySqlCommand cmd = new MySqlCommand("P_SetCompanyOpenAccountBillCheck3_Arcyun", trans.Connection);
                            cmd.CommandType = CommandType.StoredProcedure;
                            cmd.Parameters.Add("@ID_Para", MySqlDbType.Int32).Value = billID;
                            cmd.Parameters.Add("@BankName_Para", MySqlDbType.VarChar, 50).Value = "";
                            cmd.Parameters.Add("@PayReceiptID_Para", MySqlDbType.VarChar, 32).Value = 0;
                            cmd.Parameters.Add("@Status_Para", MySqlDbType.Int32).Value = 10;
                            cmd.Parameters.Add("@CheckerID_Para", MySqlDbType.Int32).Value = 0;
                            cmd.Parameters.Add("@CheckerName_Para", MySqlDbType.VarChar, 32).Value = "";
                            cmd.Parameters.Add("@CheckReason_Para", MySqlDbType.VarChar, 500).Value = "";
                            cmd.Parameters.Add("@IsSuccess_Para", MySqlDbType.Int32).Value = 0;
                            cmd.Parameters["@IsSuccess_Para"].Direction = ParameterDirection.Output;
                            if (conn.State == ConnectionState.Closed)
                            {
                                conn.Open();
                            }
                            cmd.ExecuteNonQuery();
                            Object ObjRtID = cmd.Parameters["@IsSuccess_Para"].Value;

                            result2 = (ObjRtID == null || ObjRtID.ToString() == string.Empty ? -5 : (int)ObjRtID);

                            exeCount++;
                        }

                        if (result2 > 0)
                        {
                            trans.Commit();
                        }
                        else
                        {
                            trans.Rollback();
                        }
                        return result2;
                    }
                    catch (Exception ex)
                    {
                        trans.Rollback();
                        ActCommon.Log.WriteLog("SetBrandCompanyOpenAccountBillCheck(date:'" + System.DateTime.Now + "'),error:" + ex.Message, "AddCompany");
#if DEBUG
                        throw ex;
#else
                        return -10000;
#endif
                    }
                    finally
                    {
                        trans.Dispose();
                        conn.Close();
                    }
                }
            }
            else
            {
                return result;
            }
        }

        /// <summary>
        /// 同步SOP的信息到POS
        /// </summary>
        /// <returns></returns>
        private int SyncPOSInfoFromSOP(ProductKey Ent)
        {

            using (MySqlConnection conn = SqlHelper.Settings.GetBrandMySqlConnection(false))
            {
                if (conn.State == ConnectionState.Closed)
                {
                    conn.Open();
                }
                MySqlTransaction trans = conn.BeginTransaction();
                try
                {
                    int rowCount = SyncVipUserInfoToPOS(Ent, trans);
                    if (rowCount > 0)
                    {
                        string vipUser = Ent.VipUserID.Substring(0, 6);
                        int result = SyncSOPFromVipTemp(vipUser, trans);
                        if (result > 0)
                        {
                            trans.Commit();
                            //配置报表
                            int result_addreport = AddOpeningUser(vipUser);
                            return 5;
                        }
                        else
                        {
                            trans.Rollback();
                            return result;
                        }
                    }
                    else
                    {
                        trans.Rollback();
                        return -10;//信息同步失败
                    }
                }
                catch (Exception ex)
                {
                    trans.Rollback();
                    ActCommon.Log.WriteLog("SyncPOSInfoFromSOP(date:'" + System.DateTime.Now + "'),error:" + ex.Message, "AddCompany");
#if DEBUG
                    throw ex;
#else
                    return -10000;
#endif
                }
                finally
                {
                    trans.Dispose();
                    conn.Close();
                }
            }
        }
        /// <summary>
        /// 开户时配置默认报表
        /// </summary>
        /// <returns></returns>
        private int AddOpeningUser(string vipUserID)
        {

            using (MySqlConnection conn = DAL.SqlHelper.Settings.GetReportMySqlConnection(true))
            {
                try
                {
                    MySqlCommand cmd = new MySqlCommand("P_AddOpeningUser", conn);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add("@BossID_Para", MySqlDbType.VarChar, 6).Value = vipUserID;
                    cmd.Parameters.Add("@IsSuccess_Para", MySqlDbType.Int32).Value = 0;
                    cmd.Parameters["@IsSuccess_Para"].Direction = ParameterDirection.Output;
                    if (conn.State == ConnectionState.Closed)
                    {
                        conn.Open();
                    }
                    cmd.ExecuteNonQuery();
                    Object ObjRtID = cmd.Parameters["@IsSuccess_Para"].Value;

                    return ObjRtID == null || ObjRtID.ToString() == string.Empty ? -5 : (int)ObjRtID;
                }
                catch (Exception ex)
                {
                    ActCommon.Log.WriteLog("AddOpeningUser(date:'" + System.DateTime.Now + "'),error:" + ex.Message, "AddCompany");
#if DEBUG
                    throw ex;
#else
                    return -10000;
#endif
                }
                finally
                {
                    conn.Close();
                }
            }
        }
        /// <summary>
        /// 同步POS用户信息
        /// </summary>
        /// <param name="modelList"></param>
        /// <returns></returns>
        private int SyncVipUserInfoToPOS(ProductKey model, MySqlTransaction trans)
        {
            StringBuilder sb = new StringBuilder();
            sb.AppendFormat("DELETE FROM CR_VIPUserTemp;");
            sb.AppendFormat("INSERT INTO CR_VIPUserTemp(VipUserID,UserName,Password,KeyBarcode,AuthKey,UserType,StoreID,StoreName,Company,Addresses,PostCode,LinkMan,LinkTel,LinkMobile,EmergencyContact,EmergencyTelephone,ServiceEndTime,DelayType,VipUserStatus,AddTime,AreaSize,AreaID,AreaName,EMail,OpenType,Remark,OpenApplyType,CompanyCode,StoreLable,CounterNum,BrandType,BossVersionType) VALUES");

            string userName = model.UserType == 10 ? model.ProductUserName : (model.UserType == 20 ? "HQ" + model.ProductUserName : "SP" + model.ProductUserName);
            sb.AppendFormat("('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}','{13}','{14}','{15}','{16}','{17}','{18}',now(),'{19}','{20}','{21}','{22}','{23}','{24}','{25}','{26}','{27}','{28}','{29}','{30}');", model.VipUserID, userName, model.Password, model.KeyBarcode, model.AuthKey, model.UserType, model.StoreID, model.StoreName, model.CompanyName, model.Addresses, model.PostCode, model.LinkMan, model.LinkTel, model.LinkMobile, model.EmergencyContact, model.EmergencyTelephone, model.ServiceEndTime, model.DelayType, model.VipUserStatus, model.AreaSize, model.AreaID, model.AreaName, model.EMail, model.OpenType, model.Remark, model.OpenApplyType, model.CompanyCode, model.StoreLable, model.StoreNum, model.BossType, model.BossVersionType);

            sb.AppendFormat("INSERT into SOP_VIPUserAccount (VipUserID,EveryMonthCost,BillingBeginDate) VALUES ('{0}','{1}','{2}');", model.VipUserID, 50, DateTime.Parse("1900-01-01 00:00:00"));
            string strsql = sb.ToString();
            MySqlCommand cmd = new MySqlCommand(strsql, trans.Connection);
            cmd.CommandType = CommandType.Text;
            return cmd.ExecuteNonQuery();

        }
        /// <summary>
        /// 同步信息到POS
        /// </summary>
        /// <returns></returns>
        private int SyncSOPFromVipTemp(string vipUserID, MySqlTransaction trans)
        {
            MySqlCommand cmd = new MySqlCommand("SOP_SyncSOPFromVipTemp", trans.Connection);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@VipUserID_Para", MySqlDbType.VarChar, 9).Value = vipUserID;
            cmd.Parameters.Add("@IsSuccess_Para", MySqlDbType.Int32).Value = 0;
            cmd.Parameters["@IsSuccess_Para"].Direction = ParameterDirection.Output;
            cmd.ExecuteNonQuery();
            Object ObjRtID = cmd.Parameters["@IsSuccess_Para"].Value;

            return ObjRtID == null || ObjRtID.ToString() == string.Empty ? -5 : (int)ObjRtID;
        }

        /// <summary>
        /// 审核app
        /// </summary>
        /// <param name="Pemissions"></param>
        /// <returns>5成功，-5失败，-10000系统错误</returns>
        public int CheckAppApplyByID(string BossID)
        {
            string AppIDList=CheckAppApplyByID1(BossID);
            CheckAppApplyByID2(BossID,AppIDList);
            SynAppSOPToPOS(BossID, AppIDList);
            return 5;
        }

        public string  CheckAppApplyByID1(string BossID)
        {
            string AppIDList = "";
            using (MySqlConnection conn = DAL.SqlHelper.Settings.GetSOPMySqlConnection(true))
            {
                if (conn.State == ConnectionState.Closed) { conn.Open(); }
                try
                {

                    StringBuilder strSql = new StringBuilder();
                    StringBuilder strSqlUpdateSOP = new StringBuilder();
                    StringBuilder strSqlUpdatePOS = new StringBuilder();
                    strSql.Append("SELECT id,AppID FROM SOP_AppApply ");
                    strSql.Append(" where VipUserID=" + BossID);
                    MySqlCommand cmd = new MySqlCommand(strSql.ToString(), conn);
                    cmd.CommandType = CommandType.Text;
                    if (conn.State == ConnectionState.Closed)
                    {
                        conn.Open();
                    }
                    MySqlDataReader sdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                    if (sdr != null)
                    {
                        while (sdr.Read())
                        {
                            if (sdr["AppID"] != DBNull.Value)
                            {
                                AppIDList += sdr["AppID"].ToString() + ",";
                            }
                        }
                    }
                    if (AppIDList.Length > 0) 
                    {
                        AppIDList = AppIDList.Substring(0, AppIDList.Length - 1);
                    }
                    return AppIDList;
                }
                catch (Exception ex)
                {
                    ActCommon.Log.WriteLog("CheckAppApplyByID(date:'" + System.DateTime.Now + "'),error:" + ex.Message, "AddCompany");
#if DEBUG
                    throw ex;
#else
                    return "-10000";
#endif
                }
                finally
                {
                    conn.Close();
                }
            }
        }

       
        /// <summary>
        /// sop同步单品牌
        /// </summary>
        /// <param name="Pemissions"></param>
        /// <returns>5成功，-5失败，-10000系统错误</returns>
        public int CheckAppApplyByID2(string BossID, String AppID)
        {
            using (MySqlConnection conn = SqlHelper.Settings.GetSOPMySqlConnection(true))
            {
                if (conn.State == ConnectionState.Closed) { conn.Open(); }
                try
                {
                    MySqlCommand cmd = new MySqlCommand("P_CheckAppApplyByID_arcyun", conn);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add("@ID_Para", MySqlDbType.VarChar,500).Value = AppID;
                    cmd.Parameters.Add("@Status_Para", MySqlDbType.Int32).Value = 10;
                    cmd.Parameters.Add("@BossID_Para", MySqlDbType.VarChar, 6).Value = BossID;
                    cmd.Parameters.Add("@CheckerID_Para", MySqlDbType.Int32).Value = 0;
                    cmd.Parameters.Add("@CheckerName_Para", MySqlDbType.VarChar, 32).Value = "";
                    cmd.Parameters.Add("@CheckReason_Para", MySqlDbType.VarChar, 255).Value = "";
                    cmd.Parameters.Add("@IsSuccess_Para", MySqlDbType.Int32).Value = 0;
                    cmd.Parameters["@IsSuccess_Para"].Direction = ParameterDirection.Output;
                    cmd.ExecuteNonQuery();

                    Object objRtID = cmd.Parameters["@IsSuccess_Para"].Value;
                    return objRtID == null || objRtID.ToString() == string.Empty ? -5 : (int)objRtID;

                }
                catch (Exception ex)
                {
                    ActCommon.Log.WriteLog("CheckAppApplyByID2(date:'" + System.DateTime.Now + "'),error:" + ex.Message, "AddCompany");
#if DEBUG
                    throw ex;
#else
                    return -10000;
#endif
                }
                finally
                {
                    conn.Close();
                }
            }
        }
        /// <summary>
        /// sop同步单品牌
        /// </summary>
        /// <param name="Pemissions"></param>
        /// <returns>5成功，-5失败，-10000系统错误</returns>
        public int SynAppSOPToPOS(string BossID, String AppID)
        {
            using (MySqlConnection conn = SqlHelper.Settings.GetBrandMySqlConnection(false))
            {
                if (conn.State == ConnectionState.Closed) { conn.Open(); }
                try
                {
                    MySqlCommand cmd = new MySqlCommand("SOPP_SynAppSOPToPOS_arcyun", conn);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add("@AppID_Para", MySqlDbType.VarChar,500).Value = AppID;
                    cmd.Parameters.Add("@BossID_Para", MySqlDbType.VarChar, 6).Value = BossID;
                    cmd.Parameters.Add("@IsSuccess_Para", MySqlDbType.Int32).Value = 0;
                    cmd.Parameters["@IsSuccess_Para"].Direction = ParameterDirection.Output;
                    cmd.ExecuteNonQuery();

                    Object objRtID = cmd.Parameters["@IsSuccess_Para"].Value;
                    return objRtID == null || objRtID.ToString() == string.Empty ? -5 : (int)objRtID;

                }
                catch (Exception ex)
                {
                    ActCommon.Log.WriteLog("SynAppSOPToPOS(date:'" + System.DateTime.Now + "'),error:" + ex.Message, "AddCompany");
#if DEBUG
                    throw ex;
#else
                    return -10000;
#endif
                }
                finally
                {
                    conn.Close();
                }
            }
        }
        #endregion

        #region 通过UserId查询
        public IList<SOP_ProductKey> GetProductKeyByUserId(int UserId)
        {
            IList<SOP_ProductKey> _getlist = new List<SOP_ProductKey>();
            using (MySqlConnection conn = SqlHelper.Settings.GetSOPMySqlConnection(false))
            {

                try
                {
                    StringBuilder strSql = new StringBuilder();
                    strSql.Append("select * from SOP_ProductKey where UserId=@UserId_Para ORDER BY ID DESC");
                    MySqlCommand cmd = new MySqlCommand(strSql.ToString(), conn);
                    cmd.CommandType = CommandType.Text;
                    cmd.Parameters.Add("@UserId_Para", MySqlDbType.Int32).Value = UserId;
                    if (conn.State == ConnectionState.Closed) { conn.Open(); }

                    MySqlDataReader sdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                    if (sdr != null)
                    {
                        while(sdr.Read()){
                            SOP_ProductKey Ent = new SOP_ProductKey();
                            Ent.ID = Convert.ToInt32(sdr["ID"].ToString());
                            Ent.VipUserID = sdr["VipUserID"].ToString();

                            Ent.CompanyName = sdr["CompanyName"].ToString();
                            Ent.BrandIcon = sdr["BrandIcon"].ToString();
                            if (!string.IsNullOrWhiteSpace(sdr["CompanyId"].ToString()))
                                Ent.CompanyId = Convert.ToInt32(sdr["CompanyId"].ToString());
                            else
                                Ent.CompanyId = 0;
                            Ent.LoginAccount = sdr["LoginAccount"].ToString();
                            _getlist.Add(Ent);
                        }
                    }

                }
                catch (Exception ex)
                {
                    ActCommon.Log.WriteLog("GetProductKeyByUserId(date:'" + System.DateTime.Now + "'),error:" + ex.Message, "AddCompany");
                    return null;
                }
                finally
                {
                    conn.Close();
                }
            }

            return _getlist;

        }
        #endregion

        #region 根据bossid获取SOP_ProductKey的ID
        public int GetSOP_ProductKeyID(string bossid)
        {
            using (MySqlConnection conn = SqlHelper.Settings.GetSOPMySqlConnection(false))
            {
                try
                {
                    StringBuilder sb = new StringBuilder();
                    sb.AppendFormat("SELECT ID FROM SOP_ProductKey WHERE VipUserID='{0}'", bossid);
                    string strsql = sb.ToString();
                    int pkID = 0;
                    MySqlCommand cmd = new MySqlCommand(strsql, conn);
                    cmd.CommandType = CommandType.Text;
                    if (conn.State == ConnectionState.Closed)
                    {
                        conn.Open();
                    }
                    using (MySqlDataReader sdr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                    {

                        while (sdr.Read())
                        {
                            if (sdr["ID"] != DBNull.Value)
                                pkID = Convert.ToInt32(sdr["ID"]);
                        }
                    }
                    return pkID;
                }
                catch (Exception ex)
                {
#if DEBUG
                    throw ex;
#else
                    return -100;
#endif
                }
                finally
                {
                    conn.Close();
                }
            }
        }
        #endregion

        #region 根据bossid获取SOP_ProductKey的ID和BrandName,CompanyName
        public int GetSOP_ProductKeyName(string bossid, out string brandname, out string companyname)
        {
            brandname = ""; companyname = "";
            using (MySqlConnection conn = SqlHelper.Settings.GetSOPMySqlConnection(false))
            {
                try
                {
                    StringBuilder sb = new StringBuilder();
                    sb.AppendFormat("SELECT ID,BrandName,CompanyName FROM SOP_ProductKey WHERE VipUserID=@BossID_Para");
                    string strsql = sb.ToString();
                    int pkID = 0;
                    MySqlCommand cmd = new MySqlCommand(strsql, conn);
                    cmd.CommandType = CommandType.Text;
                    cmd.Parameters.Add("@BossID_Para", MySqlDbType.VarChar, 20).Value = bossid;
                    if (conn.State == ConnectionState.Closed)
                    {
                        conn.Open();
                    }
                    using (MySqlDataReader sdr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                    {

                        while (sdr.Read())
                        {
                            if (sdr["ID"] != DBNull.Value)
                                pkID = Convert.ToInt32(sdr["ID"]);
                            if (sdr["BrandName"] != DBNull.Value)
                                brandname = sdr["BrandName"].ToString();
                            if (sdr["CompanyName"] != DBNull.Value)
                                companyname = sdr["CompanyName"].ToString();
                        }
                    }
                    return pkID;
                }
                catch (Exception ex)
                {
#if DEBUG
                    throw ex;
#else
                    return -100;
#endif
                }
                finally
                {
                    conn.Close();
                }
            }
        }
        #endregion
    }
}
